package com.gmrz.uaf.db.dao;

import com.gmrz.uaf.common.Constants;
import com.gmrz.uaf.db.DAOException;
import com.gmrz.uaf.db.UAFDAOFactory;
import com.gmrz.uaf.protocol.v1.json.UAFSchemaBuilder;
import com.gmrz.uaf.protocol.v1.processor.exception.UAFErrorCode;
import com.gmrz.uaf.protocol.v1.schema.*;
import com.gmrz.util.Convert;
import com.gmrz.util.Strings;
import com.gmrz.util.UUIDGenerator;
import com.gmrz.util.db.DBUtil;
import com.google.common.reflect.TypeToken;
import com.google.gson.Gson;
import com.google.inject.assistedinject.Assisted;
import com.google.inject.assistedinject.AssistedInject;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.*;

public class BaseAuthenticatorDAO implements AuthenticatorDAO {
    private static final String _SQL_UPDATE = "update t_authenticators set status=?,updatets=? where tenant_id=? and user_name=? and auth_type=? and trans_type=? and status=?";
    private static final String _SQL_UPDATE_OTHER_BY_DEVICEID = "update t_authenticators set status=?,updatets=? where tenant_id=? and user_name=? and auth_type=? and trans_type=? and status=? and device_id = ?";

    @Override
    public void updateOther(String userName, String appID, String authType, String transType) throws DAOException {
        PreparedStatement s = null;
        try {
            s = this.conn_.prepareStatement(_SQL_UPDATE);
            s.setInt(1, Constants.AuthenticatorStatus.DISABLED.getStatus());
            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime()
                    .getTime());
            s.setTimestamp(2, currentTimestamp);
            s.setString(3, appID);
            s.setString(4, userName);
            s.setString(5, authType);
            s.setString(6, transType);
            s.setInt(7, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            s.executeUpdate();
        } catch (SQLException e) {
            LOG.trace(e);
            throw new DAOException(UAFErrorCode.DB_UPDATE_AUTHENTICATOR_FAILED, e);
        } finally {
            DBUtil.close(s);
        }
    }
    @Override
    public void updateOtherByDeviceID(String userName, String appID, String authType, String transType,String deviceID) throws DAOException {
        PreparedStatement s = null;
        try {
            s = this.conn_.prepareStatement(_SQL_UPDATE_OTHER_BY_DEVICEID);
            s.setInt(1, Constants.AuthenticatorStatus.DISABLED.getStatus());
            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime()
                    .getTime());
            s.setTimestamp(2, currentTimestamp);
            s.setString(3, appID);
            s.setString(4, userName);
            s.setString(5, authType);
            s.setString(6, transType);
            s.setInt(7, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            s.setString(8,deviceID);
            s.executeUpdate();
        } catch (SQLException e) {
            LOG.trace(e);
            throw new DAOException(UAFErrorCode.DB_UPDATE_AUTHENTICATOR_FAILED, e);
        } finally {
            DBUtil.close(s);
        }
    }

    @Override
    public void addUvi(String appID, String userName, String aaid, String keyID, String uvsJson, String uvi) throws DAOException {
        int retVal = -1;

        String q = "update t_authenticators set uvs = ? , uvi = ? " + _SQL_WHERE_TENANT_AAID_KEYID_STATUS;

        PreparedStatement s = null;
        try {
            s = this.conn_.prepareStatement(q);
            byte[] uvsJsonBytes = org.bouncycastle.util.Strings.toUTF8ByteArray(uvsJson);
            String uvsJsonString = org.bouncycastle.util.Strings.fromUTF8ByteArray(uvsJsonBytes);
            byte[] uviBytes = org.bouncycastle.util.Strings.toUTF8ByteArray(uvi);
            String uviString = org.bouncycastle.util.Strings.fromUTF8ByteArray(uviBytes);
            byte[] keyIDBytes = org.bouncycastle.util.Strings.toUTF8ByteArray(keyID);
            String keyIDString = org.bouncycastle.util.Strings.fromUTF8ByteArray(keyIDBytes);

            s.setString(1, getString(uvsJsonString));
            s.setString(2, getString(uviString));
            s.setString(3, getString(appID));
            s.setString(4, getString(userName));
            s.setString(5, getString(aaid));
            s.setString(6, getString(keyIDString));
            s.setInt(7, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            retVal = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    q);
            throw new DAOException(UAFErrorCode.DB_UPDATE_AUTHENTICATOR_UVS_FAILED,
                    sqle);
        } finally {
            DBUtil.close(s);
        }

    }

    private static final String INSERT_CERTIFICATE = " insert into t_certificate (crt_id,auth_id,keyid,cert_id,cert_text," +
            "cert_no,data1,data2,createts,tenant_id,device_id,auth_type,trans_type,aaid,is_repeat,secure_level) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    @Override
    public void insertCertificate(CertificateBean certificateBean) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            int offset = 1;
            s = this.conn_.prepareStatement(INSERT_CERTIFICATE);
            s.setString(offset++, certificateBean.getCrtId());
            s.setString(offset++, certificateBean.getAuthId());
            s.setString(offset++, certificateBean.getKeyId());
            s.setString(offset++, certificateBean.getCertId());
            s.setString(offset++, certificateBean.getCertText());
            s.setLong(offset++, certificateBean.getRegCounter());
            s.setString(offset++, certificateBean.getData1());
            s.setString(offset++, certificateBean.getData2());
            s.setTimestamp(offset++, certificateBean.getCreates());
            s.setString(offset++,certificateBean.getTenantId());
            s.setString(offset++,certificateBean.getDeviceId());
            s.setString(offset++,certificateBean.getAuthType());
            s.setString(offset++,certificateBean.getTransType());
            s.setString(offset++,certificateBean.getAaid());
            s.setString(offset++,certificateBean.getIsRepeat());
            s.setInt(offset++,certificateBean.getSecureLevel());
            s.executeUpdate();

        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    INSERT_CERTIFICATE);
            LOG.error("The following SQL statement failed to execute " + INSERT_CERTIFICATE, sqle);
            throw new DAOException(UAFErrorCode.DB_INSERT_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public List<UserInfoBean> findUser(String hashedUserName, String appID, String deviceID, List<String> authType, List<String> transType) throws DAOException {
        List<UserInfoBean> resultList = new ArrayList<UserInfoBean>();
        String sql = new String("select auth_id,aaid,auth_type,cust_no,device_id,key_format,keyid,mobile,reg_counter,sign_counter,tenant_id,trans_type,user_name from t_authenticators where user_name = ? and tenant_id = ? and status = ?");
        String authTypeSql = " and auth_type in (";
        for (int i = 0; i < authType.size(); i++) {
            authTypeSql = StringUtils.join(authTypeSql , "? ,");
        }
        authTypeSql = authTypeSql.substring(0, authTypeSql.length() - 1);
        authTypeSql = StringUtils.join(authTypeSql , ") ");
        sql = StringUtils.join(sql,authTypeSql);

        String transTypeSql = " and trans_type in (";
        for (int i = 0; i < transType.size(); i++) {
            transTypeSql = StringUtils.join(transTypeSql , "? ,");
        }
        transTypeSql = transTypeSql.substring(0, transTypeSql.length() - 1);
        transTypeSql = StringUtils.join(transTypeSql , ") ");
        sql = StringUtils.join(sql,transTypeSql);

        String deviceIDSql = " and device_id = ?";

        if (Strings.isNotBlank(deviceID)) {
            sql = StringUtils.join(sql,deviceIDSql);
        }
        byte[] bytes = org.bouncycastle.util.Strings.toUTF8ByteArray(sql.toString());
        String sqlString = org.bouncycastle.util.Strings.fromUTF8ByteArray(bytes);
        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            int index = 1;
            s = this.conn_.prepareStatement(sqlString);
            s.setString(index++, hashedUserName);
            s.setString(index++, appID);
            s.setInt(index++, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            for (String auth : authType) {
                s.setString(index++, auth);
            }
            for (String tran : transType) {
                s.setString(index++, tran);
            }
            if (Strings.isNotBlank(deviceID)) {
                s.setString(index++, deviceID);
            }

            rs = s.executeQuery();
            UserInfoBean userInfoBean = null;
            while (rs.next()) {
                userInfoBean = new UserInfoBean();
                userInfoBean.setAuthId(rs.getString("auth_id"));
                userInfoBean.setAaid(rs.getString("aaid"));
                userInfoBean.setAuthType(getString(rs.getString("auth_type")));
                userInfoBean.setCustNo(rs.getString("cust_no"));
                userInfoBean.setDeviceId(getString(rs.getString("device_id")));
                userInfoBean.setKeyFormat(rs.getString("key_format"));
                userInfoBean.setKeyid(rs.getString("keyid"));
                userInfoBean.setMobile(rs.getString("mobile"));
                userInfoBean.setRegCounter(rs.getInt("reg_counter"));
                userInfoBean.setSignCounter(rs.getInt("sign_counter"));
                userInfoBean.setTenantId(getString(rs.getString("tenant_id")));
                userInfoBean.setTransType(getString(rs.getString("trans_type")));
                userInfoBean.setUserName(getString(rs.getString("user_name")));
                resultList.add(userInfoBean);
            }
            return resultList;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    public String getString(String str){
        byte[] strBytes = org.bouncycastle.util.Strings.toUTF8ByteArray(str);
        String string = org.bouncycastle.util.Strings.fromUTF8ByteArray(strBytes);
        return com.alibaba.druid.util.StringUtils.subString( StringUtils.trim(StringUtils.join(string,"")), null, null);
    }

    @Override
    public CertificateBean findCertByAuthId(String authId) throws DAOException {
        String sql = "SELECT crt_id,auth_id,keyid,cert_id,cert_text,cert_no,data1,data2,createts from t_certificate WHERE auth_id = ?";
        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            int index = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(index++, authId);
            rs = s.executeQuery();
            UserInfoBean userInfoBean = null;
            while (rs.next()) {
                CertificateBean certificateBean = new CertificateBean();
                certificateBean.setAuthId(rs.getString("auth_id"));
                certificateBean.setCertId(rs.getString("cert_id"));
                certificateBean.setCertText(rs.getString("cert_text"));
                certificateBean.setCreates(rs.getTimestamp("createts"));
                certificateBean.setCrtId(rs.getString("crt_id"));
                certificateBean.setData1(rs.getString("data1"));
                certificateBean.setData2(rs.getString("data2"));
                certificateBean.setKeyId(rs.getString("keyid"));
                certificateBean.setRegCounter(rs.getInt("cert_no"));
                return certificateBean;
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return null;
    }

    @Override
    public List<CertificateBean> findCertByDeviceId(String deviceID,int secureLevel) throws DAOException {
        String sql = "SELECT crt_id,auth_id,keyid,cert_id,cert_text,cert_no,data1,data2,createts  \n" +
                "from t_certificate WHERE device_id = ? and secure_level = ? ";
        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            int index = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(index++, deviceID);
            s.setInt(index++, secureLevel);
            rs = s.executeQuery();
            UserInfoBean userInfoBean = null;
            List<CertificateBean> certificateBeans = new ArrayList<CertificateBean>();
            while (rs.next()) {
                CertificateBean certificateBean = new CertificateBean();
                certificateBean.setAuthId(rs.getString("auth_id"));
                certificateBean.setCertId(rs.getString("cert_id"));
                certificateBean.setCertText(rs.getString("cert_text"));
                certificateBean.setCreates(rs.getTimestamp("createts"));
                certificateBean.setCrtId(rs.getString("crt_id"));
                certificateBean.setData1(rs.getString("data1"));
                certificateBean.setData2(rs.getString("data2"));
                certificateBean.setKeyId(rs.getString("keyid"));
                certificateBean.setRegCounter(rs.getInt("cert_no"));
                certificateBeans.add(certificateBean);
            }
            return certificateBeans;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public long findMaxCount() throws DAOException {
        String sql = "SELECT count(1) max_no from t_certificate where is_repeat = 0 ";
        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            s = this.conn_.prepareStatement(sql);
            rs = s.executeQuery();
            while (rs.next()) {
                return rs.getLong("max_no");
            }
            return 0;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QIERY_CERTIFICATE_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }


    @Override
    public long findMaxCount(String securityLevel) throws DAOException {
        String sql = "SELECT count(1) max_no from t_certificate where is_repeat = 0  " +
                "and aaid in (select dict_name from t_dict where dict_text = ?)";
        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, securityLevel);
            rs = s.executeQuery();
            while (rs.next()) {
                return rs.getLong("max_no");
            }
            return 0;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QIERY_CERTIFICATE_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void deleteCert(String authId) throws DAOException {
        String sql = "DELETE from t_certificate WHERE auth_id = ?";

        PreparedStatement s = null;
        try {
            s = this.conn_.prepareStatement(sql);


            s.setString(1, authId);
            int count = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
        } finally {
            DBUtil.close(s);
        }
    }

    private static final Logger LOG = LogManager
            .getLogger(BaseAuthenticatorDAO.class);
    private Connection conn_;

    private static final String _SQL_SELECT_ALL =
            "SELECT auth_id,user_name,cust_no,metadata_id,aaid,keyid,pubkey," +
                    "key_format,sign_counter,reg_counter," +
                    "device_id,status,png_char,auth_type," +
                    "trans_type,status,createts,updatets," +
                    "tenant_id,uvi,uvs,mobile ,rf1,rf2,fido_pubkey  FROM t_authenticators ";

    private static final String _SQL_WHERE_TENANT_USERNAME_STATUS = " WHERE tenant_id=? AND user_name=?  AND status=?";
    private static final String _SQL_WHERE_TENANT_USERNAME_DEVICE_STATUS = "WHERE tenant_id=? AND user_name=?  AND device_id=? AND status=? ORDER BY updatets DESC ";
    private static final String _SQL_WHERE_TENANT_USERNAME_AUTHTYPE_STATUS = "WHERE tenant_id=? AND user_name=?  AND auth_type in (?,?) AND status=? ORDER BY updatets DESC ";
    private static final String _SQL_WHERE_TENANT_USERNAME_DEVICE_AUTHTYPE_TRANSTYPE_STATUS =
            "where tenant_id=? and user_name=? and auth_type=? and trans_type=? and  device_id=? and status=?";
    private static final String _SQL_WHERE_TENANT_USERNAME_AUTHTYPE_TRANSTYPE_STATUS =
            "where tenant_id=? and user_name=? and auth_type=? and trans_type=? and status=?";
    private static final String _SQL_WHERE_TENANT_AAID_KEYID_STATUS = " where tenant_id=? AND user_name=? and aaid=? and keyid=? and status=?";


    private static final String _SQL_DELETE = "update t_authenticators set status= ? "
            + ", updatets=?" + _SQL_WHERE_TENANT_AAID_KEYID_STATUS;

    private static final String _SQL_UPDATE_SIGN_COUNTER = "update t_authenticators set sign_counter=? , " +
            "updatets=?" + _SQL_WHERE_TENANT_AAID_KEYID_STATUS;
    private static final String _SQL_SELECT_ACTIVE_REG_COUNT = "select count(device_id) from t_authenticators" +
            _SQL_WHERE_TENANT_USERNAME_STATUS;

    private static final String _SQL_INSERT = "INSERT INTO t_authenticators (auth_id,user_name,cust_no,tenant_id," +
            "metadata_id, aaid, keyid,pubkey, key_format,sign_counter,reg_counter,uvi,uvs,device_id,auth_type,trans_type," +
            "png_char,status,createts,updatets,mobile,rf1,rf2,cust_type,reg_from,fido_pubkey,inst_code)  " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    @AssistedInject
    public BaseAuthenticatorDAO(@Assisted Connection conn_) {
        this.conn_ = conn_;
    }

    public BaseAuthenticatorDAO() {

    }
    @Override
    public void insert(Authenticator a) throws DAOException {
        String q = _SQL_INSERT;
        Gson gson = UAFSchemaBuilder.getGson();
        String metadataid;
        AuthenticatorSpec spec = a.getMetadata();
        if (spec != null) {
            metadataid = spec.getID();
            if (spec.getID() == null) {
                LOG.error(
                        "Invalid authenticator specification id:{} for AAID:{}",
                        Integer.valueOf(spec.getID()),
                        spec.getAAID());
                throw new DAOException(UAFErrorCode.DB_QUERY_METADATA_FAILED,
                        "Invalid authenticator specification id: "
                                + spec.getID() + " for AAID: " + spec.getAAID());
            }
        } else {
            metadataid = a.getSpecDBID();
        }

        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            int offset = 1;
            s = this.conn_.prepareStatement(q, 1);
            s.setString(offset++, a.getID());
            s.setString(offset++, a.getUserName().trim());
            s.setString(offset++, a.getCustNo());
            s.setString(offset++, a.getAppID());
            s.setString(offset++, metadataid);
            s.setString(offset++, a.getAAID().format());
            s.setString(offset++, Convert.toBase64(a.getKeyID().getId()));
            s.setString(offset++, a.getPubKey());
            s.setInt(offset++, a.getKeyFormat());
            s.setInt(offset++, a.getSignCounter());
            s.setInt(offset++, a.getRegCounter());
            s.setString(offset++, a.getUvi());
            String uvsval = gson.toJson(a.getUvs(), new TypeToken<List<String>>() {
            }.getType());
            LOG.info("uvsval=" + uvsval);
            s.setString(offset++, uvsval);
            s.setString(offset++, a.getDeviceID().trim());
            s.setString(offset++, a.getAuthType());
            s.setString(offset++, a.getTransType());
            s.setString(offset++, gson.toJson(a.getPngCharacteristics(),
                    new TypeToken<List<PNGCharacteristics>>() {
                    }.getType()));
            s.setInt(offset++, a.getStatus());


            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime()
                    .getTime());

            s.setTimestamp(offset++, currentTimestamp);
            s.setTimestamp(offset++, currentTimestamp);
            s.setString(offset++, a.getMobile());
            s.setString(offset++, a.getRf1());
            s.setString(offset++, a.getRf2());
            s.setString(offset++, a.getCustType());
            s.setString(offset++,a.getFrom());
            s.setString(offset++,a.getFidoPubkey());
            s.setString(offset++,a.getInstCode());
            s.executeUpdate();

        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    q);
            LOG.error("The following SQL statement failed to execute " + q, sqle);
            throw new DAOException(UAFErrorCode.DB_INSERT_AUTHENTICATOR_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    /**
     * 查询指定用户在指定应用中的所有注册信息
     *
     * @param userName
     * @param appID
     * @return
     * @throws DAOException
     */
    @Override
    public Set<Authenticator> findUserOnAllDevice(String userName, String appID) throws DAOException {
        String sql = _SQL_SELECT_ALL +
                _SQL_WHERE_TENANT_USERNAME_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setInt(3, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                retval.add(authenticator);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public Authenticator findUser(String appID, String userName, String aaid, String keyID) throws DAOException {
        String q = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_AAID_KEYID_STATUS;
        PreparedStatement s = null;
        ResultSet rs = null;
        Authenticator retval = null;
        try {
            s = this.conn_.prepareStatement(q);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, aaid);
            s.setString(4, keyID);
            s.setInt(5, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            rs = s.executeQuery();
            if (rs.next()) {
                retval = readAuthenticator(rs);
            }

            if (retval != null)
                updateMetadata(new HashSet(Collections.singletonList(retval)));
            if(retval != null){
                retval.setCertificateExtendBean(getCertificateExtend(retval.getID(),retval.getAuthType()));
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    q);
            LOG.error(sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return retval;
    }

    /**
     * 查询当前设备上用户注册信息(查询用户开通状态)
     *
     * @param userName
     * @param appID
     * @param deviceID
     * @return
     * @throws DAOException
     */
    @Override
    public List<Authenticator> findUserOnDevice(String userName, String appID, String deviceID) throws DAOException {
        //String sql = "SELECT auth_type,trans_type,status FROM t_authenticators " +
        String sql = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_USERNAME_DEVICE_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        List<Authenticator> retval = new ArrayList<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, deviceID);
            s.setInt(4, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = new Authenticator();
                authenticator.setUserName(userName);
                authenticator.setAppID(appID);
                authenticator.setAuthType(rs.getString("auth_type"));
                authenticator.setTransType(rs.getString("trans_type"));
                authenticator.setDeviceID(rs.getString("device_id"));
                authenticator.setStatus(rs.getInt("status"));
                retval.add(authenticator);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    /**
     * 查询用户远程注册信息(查询用户开通状态)
     *
     * @param userName
     * @param appID
     * @return
     * @throws DAOException
     */
    @Override
    public List<Authenticator> findUserOnRemoteAuthType(String userName, String appID) throws DAOException {
        String sql = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_USERNAME_AUTHTYPE_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        List<Authenticator> retval = new ArrayList<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, Constants.AuthType.REMOTE_FACEPRINT.getAuthType());
            s.setString(4, Constants.AuthType.REMOTE_VOICEPRINT.getAuthType());
            s.setInt(5, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = new Authenticator();
                authenticator.setUserName(userName);
                authenticator.setAppID(appID);
                authenticator.setAuthType(rs.getString("auth_type"));
                authenticator.setTransType(rs.getString("trans_type"));
                authenticator.setDeviceID(rs.getString("device_id"));
                authenticator.setStatus(rs.getInt("status"));
                retval.add(authenticator);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    /**
     * 查询指定用户在指定设备、应用、认证方式下的认证器信息(注销单个)
     *
     * @param userName
     * @param appID
     * @param deviceID
     * @param authType
     * @return
     * @throws DAOException
     */
    @Override
    public Set<Authenticator> findUserOnDevice(String userName, String appID, String deviceID,
                                               String authType, String transType, int status) throws DAOException {

        String sql = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_USERNAME_DEVICE_AUTHTYPE_TRANSTYPE_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, authType);
            s.setString(4, transType);
            s.setString(5, deviceID);
            s.setInt(6, status);
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                retval.add(authenticator);
            }
            if (status == Constants.AuthenticatorStatus.ACTIVE.getStatus()) {
                updateMetadata(retval);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }
    @Override
    public Set<Authenticator> findUserOnDeviceDer(String userName, String appID, String deviceID,
                                                  String authType, String transType, int status) throws DAOException {

        String sql = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_USERNAME_DEVICE_AUTHTYPE_TRANSTYPE_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, authType);
            s.setString(4, transType);
            s.setString(5, deviceID);
            s.setInt(6, status);
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                // 已经注册了的认证数据
                authenticator = readAuthenticator(rs);
                // 根据认证数据id 获取证书扩展数据
                authenticator.setCertificateExtendBean(getCertificateExtend(authenticator.getID(),authenticator.getAuthType()));
                retval.add(authenticator);
            }

            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    /**
     * 查询指定用户应用、认证方式下的认证器信息(注销单个)
     *
     * @param userName
     * @param appID
     * @param authType
     * @return
     * @throws DAOException
     */
    @Override
    public Set<Authenticator> findUserNoDevice(String userName, String appID,
                                               String authType, String transType, int status) throws DAOException {

        String sql = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_USERNAME_AUTHTYPE_TRANSTYPE_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, authType);
            s.setString(4, transType);
            s.setInt(5, status);
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                retval.add(authenticator);
            }
            if (status == Constants.AuthenticatorStatus.ACTIVE.getStatus()) {
                updateMetadata(retval);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    /**
     * 查询指定用户应用、认证方式下的认证器信息(注销单个)
     *
     * @param userName
     * @param appID
     * @param authType
     * @return
     * @throws DAOException
     */
    @Override
    public Set<Authenticator> findUserNoDeviceDer(String userName, String appID,
                                                  String authType, String transType, int status) throws DAOException {

        String sql = _SQL_SELECT_ALL + _SQL_WHERE_TENANT_USERNAME_AUTHTYPE_TRANSTYPE_STATUS;

        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, userName);
            s.setString(3, authType);
            s.setString(4, transType);
            s.setInt(5, status);
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                authenticator.setCertificateExtendBean(getCertificateExtend(authenticator.getID(),authenticator.getAuthType()));
                retval.add(authenticator);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }
    @Override
    public boolean delete(Authenticator a, int status) throws DAOException {
        String q = _SQL_DELETE;

        PreparedStatement s = null;
        try {
            s = this.conn_.prepareStatement(q);
            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime()
                    .getTime());

            s.setInt(1, status);
            s.setTimestamp(2, currentTimestamp);
            s.setString(3, a.getAppID());
            s.setString(4, a.getUserName());
            s.setString(5, a.getAAID().format());
            s.setString(6, Convert.toBase64(a.getKeyID().getId()));
            s.setInt(7, a.getStatus());
            int count = s.executeUpdate();
            return count > 0;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    q);
            throw new DAOException(UAFErrorCode.DB_DEL_AUTHENTICATOR_FAILED,
                    sqle);
        } finally {
            DBUtil.close(s);
        }
    }

    protected Authenticator readAuthenticator(ResultSet rs) throws DAOException {
        Authenticator retval = new Authenticator();
        Gson gson = UAFSchemaBuilder.getGson();
        try {
            int offset = 1;
            retval.setID(rs.getString(offset++));
            retval.setUserName(rs.getString(offset++));
            retval.setCustNo(rs.getString(offset++));
            String metadataid = rs.getString(offset++);
            retval.setSpecDBID(metadataid);
            retval.setAAID(new AAID().parse(rs.getString(offset++)));
            retval.setKeyID(new KeyID().withId(Convert.fromBase64(rs
                    .getString(offset++))));
            retval.setPubKey(rs.getString(offset++));
            retval.setKeyFormat(rs.getInt(offset++));
            retval.setSignCounter(rs.getInt(offset++));
            retval.setRegCounter(rs.getInt(offset++));
            retval.setDeviceID(rs.getString(offset++));
            retval.setStatus(rs.getInt(offset++));


            List<PNGCharacteristics> pngCharacteristics = gson.fromJson(
                    rs.getString(offset++),
                    new TypeToken<List<PNGCharacteristics>>() {
                    }.getType());

            if (pngCharacteristics != null)
                retval.setPngCharacteristics(pngCharacteristics);
            retval.setAppID(rs.getString("tenant_id"));
            retval.setUvi(rs.getString("uvi"));

            String uvsval = rs.getString("uvs");
            if (!Strings.isNullOrEmpty(uvsval)) {
                try {
                    List<String> uvs = gson.fromJson(
                            uvsval,
                            new TypeToken<List<String>>() {
                            }.getType());
                    if (uvs != null)
                        retval.setUvs(uvs);
                } catch (Exception e) {
                    retval.setUvs(null);
                }
            }

            retval.setAuthType(rs.getString("auth_type"));
            retval.setTransType(rs.getString("trans_type"));
            retval.setMobile(rs.getString("mobile"));
            retval.setRf1(rs.getString("rf1"));
            retval.setRf2(rs.getString("rf2"));
            retval.setCreateTimeStamp(rs.getTimestamp("createts"));
            retval.setUpdateTimeStamp(rs.getTimestamp("updatets"));
            retval.setFidoPubkey(rs.getString("fido_pubkey"));
        } catch (SQLException sqle) {
            LOG.error(sqle);
            LOG.error("Failed to read the Authenticator Spec", sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        }
        return retval;
    }

    private void updateMetadata(Set<Authenticator> authenticators)
            throws DAOException {
        AuthenticatorSpecDAO specdao = UAFDAOFactory.createAuthenticatorSpecDAO(this.conn_);
        for (Authenticator a : authenticators) {
            LOG.info("updateMetadata getSpecDBID=" + a.getSpecDBID());
            AuthenticatorSpec spec = specdao.getBySpecID(a.getSpecDBID());
            if (null == spec) {
                String errorMsg = "No spec found for " + a.getAAID().format()
                        + " in spec table";
                LOG.error(errorMsg);
                throw new DAOException(UAFErrorCode.DB_QUERY_METADATA_FAILED,
                        errorMsg);
            }
            a.setMetadata(spec);
            a.setAAID(spec.getAAID());
        }
    }
    @Override
    public void updateDevice(String userName, String appID, String deviceID, String authType, String transType) throws DAOException {
        DevicesDao devicesDao = UAFDAOFactory.createDevicesDAO(this.conn_);
        devicesDao.delete(userName, appID, deviceID, authType, transType);
    }

    @Override
    public int updateSignCounter(int signCounter, String appID, String userName, String aaid, String keyID)
            throws DAOException {
        int retVal = -1;

        String q = _SQL_UPDATE_SIGN_COUNTER;

        PreparedStatement s = null;
        try {
            s = this.conn_.prepareStatement(q);
            s.setInt(1, signCounter);

            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime()
                    .getTime());

            s.setTimestamp(2, currentTimestamp);
            s.setString(3, appID);
            s.setString(4, userName);
            s.setString(5, aaid);
            s.setString(6, keyID);
            s.setInt(7, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            retVal = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    q);
            throw new DAOException(UAFErrorCode.DB_UPDATE_AUTHENTICATOR_FAILED,
                    sqle);
        } finally {
            DBUtil.close(s);
        }

        return retVal;
    }
    @Override
    public int getActiveRegCount(String username, String tenantid) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        int retval = 0;
        try {
            s = this.conn_.prepareStatement(_SQL_SELECT_ACTIVE_REG_COUNT);
            s.setString(1, tenantid);
            s.setString(2, username);
            s.setInt(3, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            rs = s.executeQuery();
            while (rs.next())
                retval = rs.getInt(1);
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    _SQL_SELECT_ACTIVE_REG_COUNT);
            LOG.error(sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return retval;
    }

    @Override
    public int getCertActiveRegCount(String username,String authType) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        int retval = 0;
        String sql = "select count(distinct device_id) from t_authenticators WHERE  user_name=?  AND status=? and auth_type=?";
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, username);
            s.setInt(2, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            s.setString(3,authType);
            rs = s.executeQuery();
            while (rs.next())
                retval = rs.getInt(1);
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error(sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return retval;
    }

    @Override
    public boolean deleteAuth(Authenticator authenticator) throws DAOException {
        PreparedStatement s = null;
        int retval = 0;
        String sql = "delete from t_authenticators WHERE  auth_id=?  AND tenant_id='9999' ";
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, authenticator.getID());
            retval = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error(sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(s);
        }
        if(retval > 0){
            return true;
        }
        return false;
    }

    @Override
    public List<Authenticator> findAuthenticatorAndCertInfo(String userName, String deviceId, List<String> authType,String appID) throws DAOException {
        String sql = _SQL_SELECT_ALL + " WHERE user_name=? and tenant_id=?  ";

        String authTypeSql = " and auth_type in (";
        for (int i = 0; i < authType.size(); i++) {
            authTypeSql = StringUtils.join(authTypeSql , "? ,");
        }
        authTypeSql = authTypeSql.substring(0, authTypeSql.length() - 1);
        authTypeSql = StringUtils.join(authTypeSql , ") ");
        sql = StringUtils.join(sql,authTypeSql);

        if(Strings.isNotBlank(deviceId)){
            sql = sql + " AND device_id=?";
        }

        PreparedStatement s = null;
        ResultSet rs = null;
        List<Authenticator> retval = new ArrayList<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            int index =1;
            s.setString(index++, userName);
            s.setString(index++, appID);
            for (String auth : authType) {
                s.setString(index++, auth);
            }
            if(Strings.isNotBlank(deviceId)){
                s.setString(index++, deviceId);
            }

            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                retval.add(authenticator);
            }
            for(Authenticator a : retval){
                a.setCertificateExtendBean(getCertificateExtend(a.getID(),a.getAuthType()));
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public CertificateExtendBean getCertificateExtend(String authId,String authType) throws DAOException{
        String sql = "select cert_ext_id,auth_id,cert_dn,issued_time,expire_time,cert_status,activate_time,cancel_time,activate_appid,activate_type,cancel_appid,cancel_reason,cert_type,createts,serial_number from t_certificate_extend WHERE auth_id = ?";
        if (authType.compareTo(Constants.AuthType.CERT_FINGERPRINT.getAuthType()) < 0) {
            return null;
        }
        PreparedStatement s = null;
        ResultSet rs = null;
        List<Authenticator> retval = new ArrayList<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, authId);
            rs = s.executeQuery();
            CertificateExtendBean certificateExtendBean = null;
            while (rs.next()) {
                certificateExtendBean = new CertificateExtendBean();
                certificateExtendBean.setCertExtId(rs.getString("cert_ext_id"));
                certificateExtendBean.setAuthId(rs.getString("auth_id"));
                certificateExtendBean.setCertDN(rs.getString("cert_dn"));
                certificateExtendBean.setIssuedTime(rs.getTimestamp("issued_time"));
                certificateExtendBean.setExpireTime(rs.getTimestamp("expire_time"));
                certificateExtendBean.setCertStatus(rs.getInt("cert_status"));
                certificateExtendBean.setActivateTime(rs.getTimestamp("activate_time"));
                certificateExtendBean.setCancelTime(rs.getTimestamp("cancel_time"));
                certificateExtendBean.setActivateAppid(rs.getString("activate_appid"));
                certificateExtendBean.setActivateType(rs.getString("activate_type"));
                certificateExtendBean.setCancelAppid(rs.getString("cancel_appid"));
                certificateExtendBean.setCancelReason(rs.getString("cancel_reason"));
                certificateExtendBean.setCertType(rs.getString("cert_type"));
                certificateExtendBean.setCreatets(rs.getTimestamp("createts"));
                certificateExtendBean.setSerialNumber(rs.getString("serial_number"));
                return certificateExtendBean;

            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return null;
    }

    @Override
    public void insertCertificateExtend(CertificateExtendBean certificateExtendBean) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "INSERT INTO t_certificate_extend (cert_ext_id,auth_id,cert_dn,issued_time,expire_time,cert_status,cert_type,serial_number) VALUES (?,?,?,?,?,?,?,?)";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++, certificateExtendBean.getCertExtId());
            s.setString(offset++, certificateExtendBean.getAuthId());
            s.setString(offset++, certificateExtendBean.getCertDN());
            s.setTimestamp(offset++, new Timestamp(certificateExtendBean.getIssuedTime().getTime()));
            s.setTimestamp(offset++, new Timestamp(certificateExtendBean.getExpireTime().getTime()));
            s.setInt(offset++, certificateExtendBean.getCertStatus());
            s.setString(offset++, certificateExtendBean.getCertType());
            s.setString(offset++, certificateExtendBean.getSerialNumber());
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_INSERT_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public Authenticator findAuthenticator(String appID, String userName, String keyID) throws DAOException {
        String sql = _SQL_SELECT_ALL + " WHERE user_name=? and tenant_id=? and keyid=?";

        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, userName);
            s.setString(2, appID);
            s.setString(3, keyID);
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                authenticator.setCertificateExtendBean(getCertificateExtend(authenticator.getID(),authenticator.getAuthType()));
                return authenticator;
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return null;
    }

    @Override
    public int updateCertStatus(String authid,int fromStatus,int toStatus) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_certificate_extend set cert_status = ? WHERE cert_status = ? and auth_id = ?";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setInt(offset++,toStatus);
            s.setInt(offset++,fromStatus);
            s.setString(offset++, authid);
            return s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void updateCertDNIndex(String cardType, String cardName, String cardNO, int dnIndex) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_certificate_dn set dn_index = ? where card_type = ? and card_no = ? and card_name = ? ";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setInt(offset++, dnIndex);
            s.setString(offset++, cardType);
            s.setString(offset++, cardNO);
            s.setString(offset++, cardName);
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void insertCertDNIndex(String cardType, String cardName, String cardNO, int dnIndex) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "INSERT INTO t_certificate_dn (t_cert_dn_id,dn_index,card_type,card_no,card_name) VALUES (?,?,?,?,?)";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++, UUIDGenerator.generateID());
            s.setInt(offset++, dnIndex);
            s.setString(offset++, cardType);
            s.setString(offset++, cardNO);
            s.setString(offset++, cardName);
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }


    @Override
    public void insertOOBCode(OOBCodeBean oobCodeBean) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "INSERT INTO t_oob_code (oob_code_id,auth_token,scan_data,app_id,cust_no,code_status) VALUES (?,?,?,?,?,?)";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++, oobCodeBean.getOobCodeID());
            s.setString(offset++, oobCodeBean.getAuthToken());
            s.setString(offset++, oobCodeBean.getScanData());
            s.setString(offset++, oobCodeBean.getAppID());
            s.setString(offset++, oobCodeBean.getCustNO());
            s.setInt(offset++, oobCodeBean.getCodeStatus());
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_INSERT_CODE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void updateOOBCodeStatus(String authToken ,int fromStatus, int toStatus) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_oob_code set code_status = ? WHERE auth_token = ? and code_status = ?";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setInt(offset++, toStatus);
            s.setString(offset++, authToken);
            s.setInt(offset++, fromStatus);
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CODE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void updateOOBCode(OOBCodeBean oobCodeBean) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_oob_code set auth_app_id = ?, auth_time = ?, code_status = ? , device_id = ? , auth_id = ? WHERE auth_token = ? ";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++, oobCodeBean.getAuthAppID());
            s.setTimestamp(offset++, new Timestamp(System.currentTimeMillis()));
            s.setInt(offset++, oobCodeBean.getCodeStatus());
            s.setString(offset++, oobCodeBean.getDeviceID());
            s.setString(offset++, oobCodeBean.getAuthID());
            s.setString(offset++, oobCodeBean.getAuthToken());
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CODE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public OOBCodeBean findOOBCode(String authToken) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "SELECT oob_code_id,auth_token,scan_data,app_id,auth_app_id,createts,auth_time,cust_no,code_status,device_id,auth_id FROM t_oob_code WHERE auth_token = ?";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++, authToken);
            rs = s.executeQuery();
            OOBCodeBean oobCodeBean = null;
            while (rs.next()) {
                oobCodeBean = new OOBCodeBean();
                oobCodeBean.setOobCodeID(rs.getString("oob_code_id"));
                oobCodeBean.setAuthToken(rs.getString("auth_token"));
                oobCodeBean.setScanData(rs.getString("scan_data"));
                oobCodeBean.setAppID(rs.getString("app_id"));
                oobCodeBean.setAuthAppID(rs.getString("auth_app_id"));
                oobCodeBean.setCreatets(rs.getTimestamp("createts"));
                oobCodeBean.setAuthTime(rs.getTimestamp("auth_time"));
                oobCodeBean.setCustNO(rs.getString("cust_no"));
                oobCodeBean.setCodeStatus(rs.getInt("code_status"));
                oobCodeBean.setDeviceID(rs.getString("device_id"));
                oobCodeBean.setAuthID(rs.getString("auth_id"));
                return oobCodeBean;
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CODE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return null;
    }
    @Override
    public void updateCertExtend(String activateAppID ,String authID,String activateType) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_certificate_extend set activate_time = ? , activate_appid = ? , activate_type = ? WHERE auth_id = ?";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setTimestamp(offset++, new Timestamp(System.currentTimeMillis()));
            s.setString(offset++, activateAppID);
            s.setString(offset++, activateType);
            s.setString(offset++, authID);
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void insertCertUpdateRecord(String userName, String custNo, String deviceID, String appID, String authType, String transType) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "INSERT INTO t_cert_update_record(cert_update_id,user_name,cust_no,device_id,app_id,auth_type,trans_type,update_status) VALUES (?,?,?,?,?,?,?,?)";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++, UUIDGenerator.generateID());
            s.setString(offset++, userName);
            s.setString(offset++, custNo);
            s.setString(offset++, deviceID);
            s.setString(offset++, appID);
            s.setString(offset++, authType);
            s.setString(offset++, transType);
            s.setInt(offset++, 1);
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_INSERT_CERT_UPDATE_RECORD_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public void deleteCertExtend(String authID, String appID, String cancelReason) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_certificate_extend set cancel_time = ? , cancel_appid = ? , cancel_reason = ? , cert_status = ? WHERE auth_id = ?";

        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setTimestamp(offset++, new Timestamp(System.currentTimeMillis()));
            s.setString(offset++, appID);
            s.setString(offset++, cancelReason);
            s.setInt(offset++,Constants.CERT_STATUS_4);
            s.setString(offset++, authID);
            s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public int updateCertUpdateRecord(String userName, String deviceID, String authType, String transType,String authID) throws DAOException {
        PreparedStatement s = null;
        ResultSet rs = null;
        String sql = "UPDATE t_cert_update_record set update_status = 2,auth_id = ? WHERE user_name = ? and device_id = ? and auth_type = ? and trans_type = ? and update_status = 1";
        int n = 0;
        try {
            int offset = 1;
            s = this.conn_.prepareStatement(sql);
            s.setString(offset++,authID);
            s.setString(offset++, userName);
            s.setString(offset++, deviceID);
            s.setString(offset++,authType);
            s.setString(offset++, transType);
            n = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql);
            LOG.error("The following SQL statement failed to execute " + sql, sqle);
            throw new DAOException(UAFErrorCode.DB_UPDATE_CERTIFICATE_FAILED,
                    sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return n;
    }

    @Override
    public Set<Authenticator> findUserByDeviceIDAndAppID(String deviceID, String appID, String authType, String transType, int status) throws DAOException {
        String sql = _SQL_SELECT_ALL + " where tenant_id=? and device_id=? and auth_type=? and trans_type=? and status=? ";

        PreparedStatement s = null;
        ResultSet rs = null;
        Set<Authenticator> retval = new HashSet<Authenticator>();
        try {
            s = this.conn_.prepareStatement(sql);
            s.setString(1, appID);
            s.setString(2, deviceID);
            s.setString(3, authType);
            s.setString(4, transType);
            s.setInt(5, status);
            rs = s.executeQuery();
            Authenticator authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticator(rs);
                authenticator.setCertificateExtendBean(getCertificateExtend(authenticator.getID(),authenticator.getAuthType()));
                retval.add(authenticator);
            }
            return retval;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }


    @Override
    public int selectCertCountByDeviceIDAndSecurityLevel(String deviceID, String securityLevel) throws DAOException {
        String sql = "select count(*) num  from t_certificate where device_id = ? and secure_level = ? and is_repeat = 0 ";
        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            int num = 0;
            s = this.conn_.prepareStatement(sql);
            s.setString(1, deviceID);
            s.setInt(2, Integer.parseInt(securityLevel));
            rs = s.executeQuery();
            while (rs.next()) {
                num = rs.getInt("num");
            }
            if (num > 0){
                return 1;
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return 0;
    }




    @Override
    public List<AuthenticatorRegList> queryRegListByUseNameAndTenantID(String userName, String appId, String transType, String authType) throws DAOException {
        String sql = "SELECT ta.auth_id, ta.tenant_id, ta.user_name, ta.cust_no, ta.aaid, ta.keyid, ta.device_id, ta.trans_type, ta.auth_type, \n" +
                "ta.mobile, td.alias_name, td.device_type, td.device_name, td.os_version, td.os_type \n" +
                "from t_authenticators ta \n" +
                "left join t_devices td on ta.device_id = td.device_id  \n" +
                "where ta.user_name = ? and ta.tenant_id = ? ";

        if (Strings.isNotBlank(transType)){
            sql += " and ta.trans_type = ? ";
        }
        if (Strings.isNotBlank(authType)){
            sql += " and ta.auth_type = ? ";
        }

        PreparedStatement s = null;
        ResultSet rs = null;
        List<AuthenticatorRegList> authenticators = new ArrayList<AuthenticatorRegList>();

        try {

            s = this.conn_.prepareStatement(sql);
            s.setString(1, userName);
            s.setString(2, appId);
            if (Strings.isNotBlank(transType)){
                s.setString(3, transType);
            }
            if (Strings.isNotBlank(authType)){
                s.setString(4, authType);
            }
            rs = s.executeQuery();
            AuthenticatorRegList authenticator = null;
            while (rs.next()) {
                authenticator = readAuthenticatorRegList(rs);
                authenticators.add(authenticator);
            }

        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}",
                    sql, sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }

        return authenticators;
    }



    protected AuthenticatorRegList readAuthenticatorRegList(ResultSet rs) throws DAOException {
        AuthenticatorRegList retval = new AuthenticatorRegList();
        try {
            int offset = 1;
            retval.setAuthId(rs.getString(offset++));
            retval.setAppID(rs.getString(offset++));
            retval.setUserName(rs.getString(offset++));
            retval.setCustNo(rs.getString(offset++));
            retval.setAaid(new AAID().parse(rs.getString(offset++)));
            retval.setKeyID(new KeyID().withId(Convert.fromBase64(rs.getString(offset++))));
            retval.setDeviceID(rs.getString(offset++));
            retval.setTransType(rs.getString(offset++));
            retval.setAuthType(rs.getString(offset++));
            retval.setMobile(rs.getString(offset++));
            retval.setAliasName(rs.getString(offset++));
            retval.setDeviceType(rs.getString(offset++));
            retval.setDeviceName(rs.getString(offset++));
            retval.setOsVersion(rs.getString(offset++));
            retval.setOsType(rs.getString(offset++));

        } catch (SQLException sqle) {
            LOG.error(sqle);
            LOG.error("Failed to read the Authenticator Spec", sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_AUTHENTICATOR_FAILED, sqle);
        }
        return retval;
    }

}
